package com.dy.yandi.biz.dao.clicktmp;

import cn.hutool.core.util.ObjectUtil;
import com.dy.yandi.api.client.demo.model.entity.DataChartDO;
import com.dy.yandi.api.client.demo.model.vo.DataChartVO;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.data.redis.core.ValueOperations;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.math.BigInteger;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeFormatterBuilder;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @Title null.java
 * @Package com.dy.yandi.biz.dao.clicktmp
 * @Author 马嘉祺
 * @Date 2021/10/8 11:19
 * @Description
 */
@Slf4j
@Component
@RequiredArgsConstructor
public class DataChartDao {

	private static final int MATERIAL_AD_COUNT_TOPN_DEFAULT = 10;
	private static final int USED_SELLING_POINT_TOPN_DEFAULT = 5;
	private static final int SELLING_POINT_DISTRIBUTED_TOPN_DEFAULT = 5;
	private static final String MATERIAL_AD_COUNT_TOPN_KEY = "yandi:chart:topn:material_ad_count";
	private static final String USED_SELLING_POINT_TOPN_KEY = "yandi:chart:topn:used_selling_point";
	private static final String SELLING_POINT_DISTRIBUTED_TOPN_KEY = "yandi:chart:topn:selling_point_distributed";

	private static final DateTimeFormatter DATE_FORMATTER1 = new DateTimeFormatterBuilder().appendPattern("yyyyMMdd").toFormatter();
	private static final DateTimeFormatter DATE_FORMATTER2 = new DateTimeFormatterBuilder().appendPattern("yyyy-MM-dd").toFormatter();

	@Qualifier("clickhouseTemplate")
	private final JdbcTemplate clickhouseTemplate;

	@Qualifier("pigdatabaseTemplate")
	private final JdbcTemplate pigdatabaseTemplate;

	private final RedisTemplate<String, String> redisTemplate;

	public List<DataChartDO> queryMaterialCost(DataChartVO dataCharts) {

		String sDateStr = DATE_FORMATTER1.format(dataCharts.getStartDate());
		String eDateStr = DATE_FORMATTER1.format(dataCharts.getEndDate());
		String materialStr = dataCharts.getMaterialIds().stream().map(String::valueOf).collect(Collectors.joining(", ", "(", ")"));

		String sql = "SELECT\n" +
				"    CAST(round(date / 100, 0) AS String) AS period, material_id AS materialId, CAST(SUM(cost) AS Nullable(Decimal(18, 2))) AS cost\n" +
				"FROM\n" +
				"    odsmysql_ad_material am\n" +
				"    INNER JOIN v_ad_material_creative amc ON am.id=material_id\n" +
				"    INNER JOIN v_creative_day_report cdr ON amc.creative_id=cdr.creative_id\n" +
				"WHERE\n" +
				"    date >= " + sDateStr + " AND date <= " + eDateStr + " AND material_id IN " + materialStr + '\n' +
				"GROUP BY\n" +
				"    period, material_id";
		log.debug("获取绩效消耗查询: \n[{}]", sql);

		return clickhouseTemplate.query(sql, new Object[]{}, new BeanPropertyRowMapper<>(DataChartDO.class));
	}

	public List<DataChartDO> getMaterialAdCount(DataChartVO dataCharts) {
		String sDateStr = DATE_FORMATTER1.format(dataCharts.getStartDate());
		String eDateStr = DATE_FORMATTER1.format(dataCharts.getEndDate());
		Collection<Long> pgids = dataCharts.getPgids();
		Integer topNValue = getTopNValue(dataCharts.getTopn(), MATERIAL_AD_COUNT_TOPN_KEY, MATERIAL_AD_COUNT_TOPN_DEFAULT);

		String mainGameWhere = Objects.nonNull(pgids) && !pgids.isEmpty() ? pgids.stream().map(String::valueOf).collect(Collectors.joining(", ", " AND main_game_id IN (", ")")) : StringUtils.EMPTY;
		String sql1 = "SELECT\n" +
				"    material_id AS materialId, name AS materialName, user_id AS makerId, real_name AS makerName, SUM(cost) AS cost, COUNT(DISTINCT adid) AS adCount, COUNT(DISTINCT date) AS adDays\n" +
				"FROM\n" +
				"    odsmysql_ad_material am\n" +
				"    INNER JOIN v_ad_material_creative amc ON am.id=material_id\n" +
				"    INNER JOIN v_creative_day_report cdr ON amc.creative_id=cdr.creative_id\n" +
				"    LEFT JOIN odsmysql_sys_user su ON toUInt64(maker_id)=user_id\n" +
				"WHERE\n" +
				"    date >= " + sDateStr + " AND date <= " + eDateStr + " AND show_count > 0" + mainGameWhere + " AND origin=1\n" +
				"GROUP BY\n" +
				"    material_id, name, user_id, real_name\n" +
				"ORDER BY\n" +
				"    adCount DESC, adDays DESC, cost DESC\n" +
				"LIMIT\n" +
				"    " + topNValue;
		log.debug("获取素材计划数查询: \n[{}]", sql1);

		return clickhouseTemplate.query(sql1, new Object[]{}, new BeanPropertyRowMapper<>(DataChartDO.class));
	}

	public List<DataChartDO> getUsedSellingPointList(DataChartVO dataCharts) {
		String sDateStr = DATE_FORMATTER1.format(dataCharts.getStartDate());
		String eDateStr = DATE_FORMATTER1.format(dataCharts.getEndDate());
		Collection<Long> pgids = dataCharts.getPgids();
		Integer topNValue = getTopNValue(dataCharts.getTopn(), USED_SELLING_POINT_TOPN_KEY, USED_SELLING_POINT_TOPN_DEFAULT);

		String mainGameWhere = Objects.nonNull(pgids) && !pgids.isEmpty() ? pgids.stream().map(String::valueOf).collect(Collectors.joining(", ", " AND main_game_id IN (", ")")) : StringUtils.EMPTY;
		String sql = "SELECT\n" +
				"    selling_point_id AS sellingPointId, dsp.name AS sellingPointName, SUM(cost) AS cost, COUNT(DISTINCT adid) AS adCount, COUNT(DISTINCT material_id) AS materialCount\n" +
				"FROM\n" +
				"    odsmysql_ad_material am\n" +
				"    INNER JOIN odsmysql_design_selling_point dsp ON selling_point_id=dsp.id\n" +
				"    INNER JOIN v_ad_material_creative amc ON am.id=material_id\n" +
				"    INNER JOIN v_creative_day_report cdr ON amc.creative_id=cdr.creative_id\n" +
				"WHERE\n" +
				"    date >= " + sDateStr + " AND date <= " + eDateStr + " AND show_count > 0" + mainGameWhere + " AND origin=1\n" +
				"GROUP BY\n" +
				"    sellingPointId, dsp.name\n" +
				"ORDER BY\n" +
				"    cost DESC\n" +
				"LIMIT\n" +
				"    " + topNValue;
		log.debug("获取素材计划数查询: \n[{}]", sql);

		return clickhouseTemplate.query(sql, new Object[]{}, new BeanPropertyRowMapper<>(DataChartDO.class));
	}

	public List<DataChartDO> getSellingPointDistributed(DataChartVO dataCharts) {
		String sDateStr = DATE_FORMATTER2.format(dataCharts.getStartDate());
		String eDateStr = DATE_FORMATTER2.format(dataCharts.getEndDate());
		Integer makerId = dataCharts.getMakerId();
		Collection<Long> pgids = dataCharts.getPgids();
		Integer topNValue = getTopNValue(dataCharts.getTopn(), SELLING_POINT_DISTRIBUTED_TOPN_KEY, SELLING_POINT_DISTRIBUTED_TOPN_DEFAULT);

		String gameWhere = Objects.nonNull(pgids) && !pgids.isEmpty() ? pgids.stream().map(String::valueOf).collect(Collectors.joining(", ", " AND game_id IN (", ")")) : StringUtils.EMPTY;
		String sql = "SELECT\n" +
				"    dr.selling_point_id AS sellingPointId, dep.name AS sellingPointName, COUNT(DISTINCT dr.id) AS materialCount\n" +
				"FROM\n" +
				"    design_requirement dr\n" +
				"    INNER JOIN design_requirement_producer drp ON dr.id=drp.requirement_id\n" +
				"    INNER JOIN design_selling_point dep ON dr.selling_point_id=dep.id\n" +
				"WHERE\n" +
				"    producer_id=" + makerId + " AND complete_time >= '" + sDateStr + " 00:00:00' AND complete_time <= '" + eDateStr + " 23:59:59'" + gameWhere + " AND dr.status=5\n" +
				"GROUP BY\n" +
				"    dr.selling_point_id, dep.name\n" +
				"ORDER BY\n" +
				"    materialCount DESC\n" +
				"LIMIT\n" +
				"    " + topNValue;
		log.debug("获取个人素材卖点分布查询: \n[{}]", sql);

		return pigdatabaseTemplate.query(sql, new Object[]{}, new BeanPropertyRowMapper<>(DataChartDO.class));
	}

	public List<DataChartDO> getMixedStatisticsList(DataChartVO dataCharts, Integer deptId, Collection<String> groupBys) {
		String sDateStr = DATE_FORMATTER2.format(dataCharts.getStartDate());
		String eDateStr = DATE_FORMATTER2.format(dataCharts.getEndDate());
		Collection<Long> pgids = dataCharts.getPgids();
		boolean groupByMaker = groupBys.contains("makerId");

		String gameWhere = Objects.nonNull(pgids) && !pgids.isEmpty() ? pgids.stream().map(String::valueOf).collect(Collectors.joining(", ", " AND game_id IN (", ")")) : StringUtils.EMPTY;

		StringBuilder sql1 = new StringBuilder();
		sql1.append("SELECT\n");
		sql1.append("    ").append(groupByMaker ? "su.user_id AS makerId, real_name AS makerName, " : "'汇总' AS makerName, ").append("COUNT(DISTINCT pangu_material_id) AS materialCount, COUNT(DISTINCT selling_point_id) AS sellingPointCount, GROUP_CONCAT(pangu_material_id) AS attrs\n");
		sql1.append("FROM\n");
		sql1.append("    sys_user su\n");
		sql1.append("    INNER JOIN sys_user_tenant sut ON su.user_id=sut.user_id AND sut.tenant_id=7\n");
		sql1.append("    LEFT JOIN design_requirement_producer drp ON su.user_id=producer_id\n");
		sql1.append("    LEFT JOIN design_requirement dr ON requirement_id=dr.id").append(" AND complete_time >= '").append(sDateStr).append(" 00:00:00' AND complete_time<= '").append(eDateStr).append(" 23:59:59'").append(gameWhere).append(" AND dr.status=5\n");
		sql1.append("WHERE\n");
		sql1.append("    sut.dept_id=").append(deptId).append('\n');
		sql1.append(groupByMaker ? "GROUP BY\n" : StringUtils.EMPTY);
		sql1.append(groupByMaker ? "    su.user_id, real_name\n" : StringUtils.EMPTY);
		sql1.append("ORDER BY\n");
		sql1.append("    materialCount DESC, sellingPointCount DESC");
		String sqlStr1 = sql1.toString();
		log.debug("获取综合信息统计查询: \n[{}]", sqlStr1);

		List<DataChartDO> dataChartList = pigdatabaseTemplate.query(sqlStr1, new Object[]{}, new BeanPropertyRowMapper<>(DataChartDO.class));
		if (dataChartList.isEmpty()) {
			return dataChartList;
		}

		sDateStr = DATE_FORMATTER1.format(dataCharts.getStartDate());
		eDateStr = DATE_FORMATTER1.format(dataCharts.getEndDate());

		StringBuilder sql2 = new StringBuilder();
		sql2.append("SELECT\n");
		sql2.append("    ").append(groupByMaker ? "maker_id AS makerId, " : StringUtils.EMPTY).append("COUNT(DISTINCT adid) AS adCount\n");
		sql2.append("FROM\n");
		sql2.append("    v_ad_material_creative amc\n");
		sql2.append("    INNER JOIN v_creative_day_report cdr ON amc.creative_id=cdr.creative_id\n");
		sql2.append("    INNER JOIN (\n");
		sql2.append("        SELECT\n");
		sql2.append("            maker_id, CAST(arrayJoin(splitByChar(',', material_ids)) AS Nullable(UInt64)) AS material_ids\n");
		sql2.append("        FROM\n");
		sql2.append("            (\n");
		for (int idx = 0; idx < dataChartList.size(); ++idx) {
			DataChartDO dataChart = dataChartList.get(idx);
			String materialIds = ObjectUtil.defaultIfNull(dataChart.getAttrs(), StringUtils.EMPTY);
			if (idx > 0) {
				sql2.append("                UNION ALL\n");
			}
			sql2.append("                SELECT toUInt64(").append(dataChart.getMakerId()).append(") AS maker_id, '").append(materialIds).append("' AS material_ids\n");
			dataChart.setAttrs(null);
		}
		sql2.append("            )\n");
		sql2.append("    ) uam ON material_id=material_ids\n");
		sql2.append("WHERE\n");
		sql2.append("    date >= ").append(sDateStr).append(" AND date <= ").append(eDateStr).append(" AND show_count > 0\n");
		sql2.append(groupByMaker ? "GROUP BY\n" : StringUtils.EMPTY);
		sql2.append(groupByMaker ? "    maker_id" : StringUtils.EMPTY);
		String sqlStr2 = sql2.toString();
		log.debug("获取综合信息统计计划数查询: \n[{}]", sqlStr2);

		Map<Long, Integer> map = clickhouseTemplate.query(sqlStr2, new Object[]{}, new BeanPropertyRowMapper<>(DataChartDO.class)).stream().peek(e -> {
			e.setMakerId(null == e.getMakerId() ? -1 : e.getMakerId());
			e.setAdCount(null == e.getAdCount() ? 0 : e.getAdCount());
		}).collect(Collectors.toMap(DataChartDO::getMakerId, DataChartDO::getAdCount, (k1, k2) -> k1));
		for (DataChartDO dataChart : dataChartList) {
			Long makerId = null == dataChart.getMakerId() ? -1 : dataChart.getMakerId();
			dataChart.setAdCount(map.getOrDefault(makerId, BigInteger.ZERO.intValue()));
		}

		return dataChartList;
	}

	private Integer getTopNValue(Integer topN, String redisKey, Integer defaultValue) {
		if (Objects.nonNull(topN)) {
			return topN;
		}
		ValueOperations<String, String> forValue = redisTemplate.opsForValue();
		String topNStr = forValue.get(redisKey);
		if (StringUtils.isNotBlank(topNStr)) {
			return Integer.parseInt(topNStr, 10);
		}
		forValue.set(redisKey, String.valueOf(defaultValue));
		return defaultValue;
	}

}
